Skip to main content

6. Updating Our Database

Included Database Configuration​

By default, r14-evidence is configured to access the player vehicle database tables created by default in qb-core and ESX. If you are using a custom owned vehicles script, or are utilizing a resource like drx_mdw that accesses the evidence column we need to create in our table, you may need to alter the settings in our config.lua for r14-evidence. Here you may change the name of the vehicle table, the name of the plate and evidence columns in that table, and if we are using ESX or a standalone framework that needs to access player identifiers from the database the name of the table we create for it.

r14-evidence Database Config
Config.DB = { -- this config sets what table you are using to store vehicle evidence
    Vehicle = Config.Framework.QB and 'player_vehicles' or Config.Framework.ESX and 'owned_vehicles', -- set this to your player vehicle table
    Plate = 'plate', -- the column in your vehicle table that contains your license plate
    Evidence = 'evidence', -- the column in your vehicle table that contains evidence info 
    Identifiers = 'ev_identifiers', -- if you use ESX or a standalone framework and need to generate citizenids, fingerprints, and bloodtypes, set this to your custom table
}

Updating Our Player Vehicles Table​

One of the last major steps of our installation process will be the modification of our vehicle database to hold vehicle evidence information that will persist through server reset. This makes it possible for police to recover casings, blood, or fingerprints from player vehicles even after multiple server or script resets. Even if you are not experienced in managing your database, this is a relatively simple process and can be completed in just a few minutes. This guide will use the mariadb web interface included with servers maintained by ZAP Hosting, Inc but should be simliar to most database web interfaces.

danger

Never make any changes to your database without making a backup. Losing code sucks, but losing unique player data is even worse!

Accessing Our Database​

You will want to navigate to your player vehicles table, by default, this will be player_vehicles in qb-core and with most garage scripts intended to integrate seamlessly with it. We will then want to run our SQL on our table to create a new column which will store our evidence information.

Preparing to run SQL

Running our SQL Statement​

We will then want to run the following SQL statement on our table, this is intended to add a column to the end of the table named evidence which will be accessed by the resource to store and load vehicle evidence. 

ALTER TABLE `player_vehicles`
ADD `evidence` longtext;

Running our SQL

Verifying Our Change​

Once we run this SQL statement, we should recieve a notification that it has either successfully completed or that there was an error. If you experience an error please check to make sure the name of the table in the SQL statement matches your table in your database, and that no column already exists named evidence. Once we successfully run this statement however, we should verify that our database has updated and if needed alter our config in our r14-evidence resource.

Verifying our change to the database

Now that we have completed the update to our database, it is time to complete the installation checklist before restarting our server!

Player Identifiers (ESX or Standalone Frameworks ONLY)​

info

This is not a required step if you are running qb-core, a modified version of ESX, or a Standalone Framework with fingerprints, blood types, and citizenid's accessible via the framework object.

If you are running ESX or a Standalone framework and do not have fingerprints, blood type, or a citizen ID number easily accessible via your PlayerData or equivalent functions, you will need to create an evidence identifiers table that will hold these character-specific identifiers in the database so that r14-evidence may reference them when needed. Fear not, r14-evidence does not call to database everytime these need to be used, and instead simply references the database the first time a player character interacts with the server-side script.

We will want to follow the same process we did before, but this time we want to create an entirely new table within our database using the following SQL statement:

CREATE TABLE IF NOT EXISTS `ev_identifiers` (
  `identifier` varchar(255) NOT NULL,
  `citizenid` varchar(50) NOT NULL,
  `bloodtype` varchar(50) NOT NULL,
  `fingerprint` varchar(50) NOT NULL,    
   PRIMARY KEY (`identifier`)
) ENGINE=InnoDB;

Verifying Our Change​

caution

Like our entire database, it is a good idea to rouinely backup your data, if a player entry is deleted or the table is lost r14-evidence will randomly generate ENTIRELY new identifiers which means active investigations will run into issues linking evidence!

As before, we want to run this SQL statement and then verify that our database has successfully created our evidence identifiers table. In this case we are using the default name included in the config, ev_identifiers and have created some evidence with various characters. We can see that the table exists in our database, the columns match those created by the SQL, and when we create evidence with a new character they successfully have new identifiers created for them! From now on, r14-evidence will load these identifiers the first time that character interacts with the script, and will maintain the same identifiers across server reset.

Running our SQL